--网站基本信息
create table[j_site](
	[id] [int] identity(1,1),
	[name] [varchar] (100) not null,
	[logo] [varchar] (200) null,
	[keywords] [varchar] (50) null,
	[description] [varchar] (200) null,
	[address] [varchar] (200) null,
	[copyright] [varchar] (300) null,
	[enabled] [char] (1) default 'Y'
)
alter table [j_site] add constraint PK_j_site primary key (id)
alter table [j_site] add constraint UQ_j_site unique ([name])
go
--友情连接
create table[j_link](
	[id] [int] identity(1,1),
	[name] [varchar] (100) not null,
	[url] [varchar] (200) not null,
	[orderid] [int] default 0,
	[enabled] [char] (1) default 'Y'
)
alter table [j_link] add constraint PK_j_link primary key (id)
alter table [j_link] add constraint UQ_j_link unique ([name])
go
--广告栏目
create table[j_adv](
	[id] [int] identity(1,1),
	[name] [varchar] (100) not null,
	[img] [varchar] (200) null,
	[url] [varchar] (300) null,
	[type] [char] (1) not null,
	[orderid] [int] default 0,
	[enabled] [char] default 'Y'
)
alter table [j_adv] add constraint PK_j_adv primary key (id)
alter table [j_adv] add constraint UQ_j_adv unique ([name])
go
--编码表
create table[j_code](
	[id] [int] identity(1,1),
	[code] [varchar] (12) not null,
	[pCode] [varchar] (9) null,
	[name] [varchar] (50) not null, 
	[type] [varchar] (2) not null,
	[enabled] [char] default 1
)
alter table [j_code] add constraint PK_j_code primary key (id)
alter table [j_code] add constraint UQ_j_code unique (code,[type])
go

--附件表
create table[j_attach](
	[id] [int] identity(1,1),
	[pid] [int] not null,
	[name] [varchar] (200) null, 
	[table] [varchar] (50) not null,
	[url] [varchar] (200) not null
)
alter table [j_attach] add constraint PK_j_attach primary key (id)
go
--菜单
create table[j_menu](
	[code] [varchar] (12) not null,
	[pCode] [varchar] (9) null,
	[name] [varchar] (50) not null, 
	[url] [varchar] (100) null,
	[target] [varchar] (10) null,
	[isLeaf] [char] (1) not null,
	[enabled] [char] (1) default 1
)
alter table [j_menu] add constraint PK_j_menu primary key (code)
alter table [j_menu] add constraint UQ_j_menu unique (pCode,[name])
go
--函数 
CREATE FUNCTION getCodeName( @type VARCHAR(2),@code varchar(12)) 
RETURNS varchar(100) --返回值及类型
 AS 
BEGIN
	declare @val nvarchar(50)
    select @val=(select top 1 name from j_code where type=@type and code=@code  and enabled='1');
    if(@val is null)
		select @val=(select top 1 name from j_code c where type=@type and code=@code and enabled='1'); 
    RETURN @val
END 

--个人会员信息
CREATE TABLE [j_user] (
	[id] [int] identity(1,1),
	[loginName] [varchar] (20) NOT NULL ,
	[realName] [varchar] (20) NOT NULL ,
	[pwd] [varchar] (32) NOT NULL ,
	[pwd1] [varchar] (32) NULL ,
	[pwd2] [varchar] (32) NULL ,
	[pwd3] [varchar] (32) NULL ,
	[sex] [char] default 1,
	[age] [int] NULL ,
	[height] [int] null,
	[nation] [varchar] (20) null,
	[degree] [varchar] (20) null,
	[married] [char] default 0,
	[pic] [varchar] (200) null,
	[region_treeCode] [varchar] (12) null,
	[major] [varchar](2) null,
	[school] [varchar](50) null,
	[workYear] [int] default 0,
	[position] [varchar](20) null,
	[wages] [int] null,
	[wages1] [int] null,
	[wages2] [int] null,
	[workType] [varchar](20) null,
	[tel] [varchar](20) null,
	[phone] [varchar](20) null,
	[qq] [varchar](20) null,
	[email] [varchar](20) null,
	[address] [varchar](100) null,
	[summary] [varchar](500) null,
	[views] [int] default 0,
	[ip] [varchar](15) null,
	[loginTimes] [int] null,
	[enabled] [char] default 1,
	[registTime] [datetime] default getdate()
) 
alter table [j_user] add constraint PK_j_user primary key (id)
alter table [j_user] add constraint UQ_j_user unique (loginName)
go
--用户浏览公司记录
create table [j_user_to_company_history]
(
	[id] [int] identity(1,1),
	[userid] [int] not null,
	[companyid] [int] not null,
	[createTime] [datetime] default getdate()
)
alter table [j_user_to_company_history] add constraint PK_j_user_to_company_history primary key (id)
go
--用户浏览职位记录
create table [j_user_to_position_history]
(
	[id] [int] identity(1,1),
	[userid] [int] not null,
	[positionid] [int] not null,
	[createTime] [datetime] default getdate()
)
alter table [j_user_to_position_history] add constraint PK_j_user_to_position_history primary key (id)
go
--个人会员简历
create table [j_resume](
	[id] [int] identity(1,1),
	[userID] [int] not null,
	[name] [varchar] (200) not null,
	[valuate] [varchar] (500) null,
	[experience] [varchar] (1000) null,
	[education] [varchar] (500) null,
	[intention] [varchar] (500) null,
	[language] [varchar] (100) null,
	[doc] [varchar] (200) null,
	[homePage] [varchar] (200) null
)
alter table [j_resume] add constraint PK_j_resume primary key (id)
alter table [j_resume] add constraint UQ_j_resume unique (userID,name)
go
--企业信息
CREATE TABLE [j_company] (
	[id] [int] identity(1,1),
	[loginName] [varchar] (20) NOT NULL ,
	[cName] [varchar] (100) NOT NULL ,
	[pwd] [varchar] (32) NOT NULL ,
	[pwd1] [varchar] (32) NULL ,
	[pwd2] [varchar] (32) NULL ,
	[pwd3] [varchar] (32) NULL ,
	[kind] [varchar] (20) null,
	[buildDate] [varchar] (10) null,
	[money] [varchar] (20) null,
	[employee] [int] null,
	[homePage] [varchar] (200) null,
	[summary] [varchar](1000) null,
	[linkman] [varchar](20) null,
	[tel] [varchar](20) null,
	[fax] [varchar](20) null,
	[email] [varchar](20) null,
	[qq] [varchar](20) null,
	[address] [varchar](100) null,
	[postcode] [varchar](20) null,
	[views] [int] default 0,
	[ip] [varchar](15) null,
	[loginTimes] [int] null,
	[enabled] [char] null,
	[registTime] [datetime] default getdate()
) 
alter table [j_company] add constraint PK_j_company primary key (id)
alter table [j_company] add constraint UQ_j_company unique (loginName)
go
--职位信息
create table [j_position] (
	[id] [int] identity(1,1),
	[companyID] [int] not null,
	[name] [varchar] (100) NOT NULL ,
	[region] [varchar] (12) not null,
	[sex] [varchar] (2) null,
	[wages] [int] null,
	[workType] [varchar] (20) null,
	[persons] [int] null,
	[days] [int] default 30,
	[endDate] [datetime] null,
	[createTime] [datetime] default getdate(),
	[detail] [varchar] (1000) not null,
	[orderid] [int] default 0,
	[views] [int] default 0
)
alter table [j_position] add constraint PK_j_position primary key (id)
--alter table [j_position] add constraint UQ_j_position unique (companyID,name)
go

--分类
create table j_msg_class(
	[code] [varchar] (12) not null,
	[pcode] [varchar] (9) null,
	[name] [varchar] (50) not null,
	[leaf] [char] default 1,
	[enabled] [char] default 1
)
alter table [j_msg_class] add constraint PK_j_msg_class primary key (code)
go

--分类信息
create table j_msg(
	[id] [int] identity(1,1),
	[userID] [int] not null,
	[companyID] [int] not null,
	[classCode] [varchar] (12) not null,
	[title] [varchar] (100) null,
	[description] [varchar] (500) not null,
	[contact] [varchar] (20) null,
	[tel] [varchar] (20) null,
	[email] [varchar] (20) null,
	[qq] [varchar] (20) null,
	[website] [varchar] (200) null,
	[inputdate] [datetime] default getdate(),
	[enddate] [datetime] not null,
	[sessionid] [varchar] (50) null,
	[tag] [varchar] (50) null,
	[upTimes] [int] default 0,
	[downTimes] [int] default 0,
	[fix] [int] default 0,
	[views] [int] default 0
)
alter table [j_msg] add constraint PK_j_msg primary key (id)
go
--留言信息通用表
create table j_msgreply(
	[id] [int] identity(1,1),
	[pid] [int] not null,
	[tableName] [varchar] (50) not null,
	[userID] [int] not null,
	[userName] [varchar] (50) null,
	[companyID] [int] not null,
	[companyName] [varchar] (50) null,
	[title] [varchar] (100) null,
	[content] [varchar] (500) not null,
	[inputdate] [datetime] default getdate(),
	[enabled] [char] default 1
)
alter table [j_msgreply] add constraint PK_j_msgreply primary key (id)
go
--分类新闻
create table j_news(
	[id] [int] identity(1,1),
	[classid] [int] default 0,
	[userID] [int] default 0,
	[userName] [varchar] (50) null,
	[companyID] [int] default 0,
	[companyName] [varchar] (50) null,
	[title] [varchar] (100) not null,
	[content] [ntext] not null,
	[createTime] [datetime] default getdate(),
	[upTimes] [int] default 0,
	[downTimes] [int] default 0,
	[fix] [int] default 0,
	[views] [int] default 0,
	[enabled] [char] default 1
)
alter table [j_news] add constraint PK_j_news primary key (id)
go